package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblOrgBankaccountMySql;

import java.math.BigDecimal;

public class TblOrgBankaccountMySqlMapperSqlConfig {

    public String findPlanNodeListForCollection(PageInfo<TblOrgBankaccountMySql> pageInfo, TblOrgBankaccountMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT * FROM TBL_ORG_BANKACCOUNT WHERE ORGID = " + bank.getOrgid()+"");
        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND bankAccName LIKE '%" + bank.getBankaccname() + "%'");
        }
        if (bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            sbSql.append(" AND bankAccNum LIKE '%" + bank.getBankaccnum() + "%'");
        }
        if (bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            sbSql.append(" AND bankCode LIKE '%" + bank.getBankcode() + "%'");
        }
        if (bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            sbSql.append(" AND bankKhyh LIKE '%" + bank.getBankkhyh() + "%'");
        }
        if (bank.getBankname() != null && !"".equals(bank.getBankname())) {
            sbSql.append(" AND bankName LIKE '%" + bank.getBankname() + "%'");
        }
        if (bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            sbSql.append(" AND bankYhlb LIKE '%" + bank.getBankyhlb() + "%'");
        }

        sbSql.append(" ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String selectCountByPageInfoCount(TblOrgBankaccountMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT count(*) FROM TBL_ORG_BANKACCOUNT WHERE ORGID = " + bank.getOrgid()+"");
        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND bankAccName LIKE '%" + bank.getBankaccname() + "%'");
        }
        if (bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            sbSql.append(" AND bankAccNum LIKE '%" + bank.getBankaccnum() + "%'");
        }
        if (bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            sbSql.append(" AND bankCode LIKE '%" + bank.getBankcode() + "%'");
        }
        if (bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            sbSql.append(" AND bankKhyh LIKE '%" + bank.getBankkhyh() + "%'");
        }
        if (bank.getBankname() != null && !"".equals(bank.getBankname())) {
            sbSql.append(" AND bankName LIKE '%" + bank.getBankname() + "%'");
        }
        if (bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            sbSql.append(" AND bankYhlb LIKE '%" + bank.getBankyhlb() + "%'");
        }
        String sql = sbSql.toString();
        return sql;
    }


    public String findListPageInfo(PageInfo<TblOrgBankaccountMySql> pageInfo, TblOrgBankaccountMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT * FROM  TBL_ORG_BANKACCOUNT WHERE orgInfo.orgid = " + bank.getOrgid());
        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND bankAccName LIKE '%" + bank.getBankaccname() + "%'");
        }
        if (bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            sbSql.append(" AND bankAccNum LIKE '%" + bank.getBankaccnum() + "%'");
        }
        if (bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            sbSql.append(" AND bankCode LIKE '%" + bank.getBankcode() + "%'");
        }
        if (bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            sbSql.append(" AND bankKhyh LIKE '%" + bank.getBankkhyh() + "%'");
        }
        if (bank.getBankname() != null && !"".equals(bank.getBankname())) {
            sbSql.append(" AND bankName LIKE '%" + bank.getBankname() + "%'");
        }
        if (bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            sbSql.append( " AND bankYhlb LIKE '%" + bank.getBankyhlb() + "%'");
        }

        sbSql.append(" ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String findListPageInfocount(TblOrgBankaccountMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT count(*) FROM  TBL_ORG_BANKACCOUNT WHERE orgInfo.orgid = " + bank.getOrgid());
        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND bankAccName LIKE '%" + bank.getBankaccname() + "%'");
        }
        if (bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            sbSql.append(" AND bankAccNum LIKE '%" + bank.getBankaccnum() + "%'");
        }
        if (bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            sbSql.append(" AND bankCode LIKE '%" + bank.getBankcode() + "%'");
        }
        if (bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            sbSql.append(" AND bankKhyh LIKE '%" + bank.getBankkhyh() + "%'");
        }
        if (bank.getBankname() != null && !"".equals(bank.getBankname())) {
            sbSql.append(" AND bankName LIKE '%" + bank.getBankname() + "%'");
        }
        if (bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            sbSql.append( " AND bankYhlb LIKE '%" + bank.getBankyhlb() + "%'");
        }

        sbSql.append(" )");
        String sql = sbSql.toString();
        return sql;
    }


    public String findListByPageInfoPid(PageInfo<TblOrgBankaccountMySql> pageInfo, BigDecimal pid,TblOrgBankaccountMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT * FROM TBL_ORG_BANKACCOUNT WHERE ORGID = " +pid);

        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND bankAccName LIKE '%" + bank.getBankaccname() + "%'");
        }
        if (bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            sbSql.append(" AND bankAccNum LIKE '%" + bank.getBankaccnum() + "%'");
        }
        if (bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            sbSql.append(" AND bankCode LIKE '%" + bank.getBankcode() + "%'");
        }
        if (bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            sbSql.append(" AND bankKhyh LIKE '%" + bank.getBankkhyh() + "%'");
        }
        if (bank.getBankname() != null && !"".equals(bank.getBankname())) {
            sbSql.append(" AND bankName LIKE '%" + bank.getBankname() + "%'");
        }
        if (bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            sbSql.append(" AND bankYhlb LIKE '%" + bank.getBankyhlb() + "%'");
        }

        sbSql.append(" ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }


    public String findListByPageInfoPidCount(BigDecimal pid,TblOrgBankaccountMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_ORG_BANKACCOUNT WHERE ORGID = " +pid);

        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND bankAccName LIKE '%" + bank.getBankaccname() + "%'");
        }
        if (bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            sbSql.append(" AND bankAccNum LIKE '%" + bank.getBankaccnum() + "%'");
        }
        if (bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            sbSql.append(" AND bankCode LIKE '%" + bank.getBankcode() + "%'");
        }
        if (bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            sbSql.append(" AND bankKhyh LIKE '%" + bank.getBankkhyh() + "%'");
        }
        if (bank.getBankname() != null && !"".equals(bank.getBankname())) {
            sbSql.append(" AND bankName LIKE '%" + bank.getBankname() + "%'");
        }
        if (bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            sbSql.append(" AND bankYhlb LIKE '%" + bank.getBankyhlb() + "%'");
        }

        sbSql.append(" ");
        String sql = sbSql.toString();
        return sql;
    }


    public String UpdateModifyBankInfo(TblOrgBankaccountMySql oldEntity) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_ORG_BANKACCOUNT SET BANKACCNUM = '"+oldEntity.getBankaccnum()+"'");

        if(oldEntity.getBankaccname() != null && !"".equals(oldEntity.getBankaccname() )) {
            sql.append(" , BANKACCNAME = '"+oldEntity.getBankaccname() +"'");
        }
        if(oldEntity.getBankcode() != null && !"".equals(oldEntity.getBankcode())) {
            sql.append(" , BANKCODE = '"+oldEntity.getBankcode()+"'");
        }
        if(oldEntity.getBankname() != null && !"".equals(oldEntity.getBankname())) {
            sql.append(" , BANKNAME = '"+oldEntity.getBankname()+"'");
        }
        if(oldEntity.getBankkhyh() != null && !"".equals(oldEntity.getBankkhyh())) {
            sql.append(" , BANKKHYH = '"+oldEntity.getBankkhyh()+"'");
        }
        if(oldEntity.getBankyhlb() != null && !"".equals(oldEntity.getBankyhlb())) {
            sql.append(" , BANKYHLB = '"+oldEntity.getBankyhlb()+"'");
        }
        if(oldEntity.getBankstate() != null && !"".equals(oldEntity.getBankstate())) {
            sql.append(" , BANKSTATE = '"+oldEntity.getBankstate()+"'");
        }
        if(oldEntity.getBankstatus() != null && !"".equals(oldEntity.getBankstatus())) {
            sql.append(" , BANKSTATUS = '"+oldEntity.getBankstatus()+"'");
        }
        if(oldEntity.getOrgid() != null && !"".equals(oldEntity.getOrgid())) {
            sql.append(" , ORGID = '"+oldEntity.getOrgid()+"'");
        }

        sql.append(" WHERE BANKID = '"+oldEntity.getBankid()+"'");
        return sql.toString();
    }


    public String savebankInfo(TblOrgBankaccountMySql bank) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_ORG_BANKACCOUNT (BANKID");
        StringBuffer value = new StringBuffer(" VALUES (ANALUSIS_USER_SEQUENCE.nextval");

        if(bank.getBankaccnum() != null && !"".equals(bank.getBankaccnum())) {
            column.append(",BANKACCNUM");
            value.append(",'"+bank.getBankaccnum()+"'");
        }
        if(bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            column.append(",BANKACCNAME");
            value.append(",'"+bank.getBankaccname()+"'");
        }
        if(bank.getBankcode() != null && !"".equals(bank.getBankcode())) {
            column.append(",BANKCODE");
            value.append(",'"+bank.getBankcode()+"'");
        }
        if(bank.getBankkhyh() != null && !"".equals(bank.getBankkhyh())) {
            column.append(",BANKKHYH");
            value.append(",'"+bank.getBankkhyh()+"'");
        }
        if(bank.getBankyhlb() != null && !"".equals(bank.getBankyhlb())) {
            column.append(",BANKYHLB");
            value.append(",'"+bank.getBankyhlb()+"'");
        }
        if(bank.getBankstate() != null && !"".equals(bank.getBankstate())) {
            column.append(",BANKSTATE");
            value.append(",'"+bank.getBankstate()+"'");
        }
        if(bank.getBankstatus() != null && !"".equals(bank.getBankstatus())) {
            column.append(",BANKSTATUS");
            value.append(",'"+bank.getBankstatus()+"'");
        }
        if(bank.getOrgid() != null && !"".equals(bank.getOrgid())) {
            column.append(",ORGID");
            value.append(",'"+bank.getOrgid()+"'");
        }
        if(bank.getBankname() != null && !"".equals(bank.getBankname())) {
            column.append(",BANKNAME");
            value.append(",'"+bank.getBankname()+"'");
        }
        if(bank.getCreatedate() != null && !"".equals(bank.getCreatedate())) {
            column.append(",CREATEDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(bank.getCreatedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(bank.getOutsideid() != null && !"".equals(bank.getOutsideid())) {
            column.append(",OUTSIDEID");
            value.append(",'"+bank.getOutsideid()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }
}
